package vip.xiaonuo.smzq.modular.nbfb.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import vip.xiaonuo.smzq.modular.kcl.entity.Zycl;
import vip.xiaonuo.smzq.modular.nbfb.entity.vo.GbskcVo;

import java.util.List;

/**
 * 共伴生矿产
 */
@Mapper
public interface GbskcMapper extends BaseMapper<Zycl> {
    /**
     * 年报附表8
     * SQQ
     * @param nd 年度
     * @return
     */
    @Select("WITH base AS (\n" +
            "\tSELECT\n" +
            "\t\tks.nd,\n" +
            "\t\tks.kqbh,\n" +
            "\t\tks.kcmc,\n" +
            "\t\t ks.kczh,\n" +
            "\t\t ks.tjdx,\n" +
            "\tCASE\n" +
            "\t\t\t\n" +
            "\t\t\tWHEN zh.mc IS NULL \n" +
            "\t\t\tOR zh.mc = '' THEN\n" +
            "\t\t\t\t'700400' ELSE zh.dm \n" +
            "\t\t\t\tEND AS dm,\n" +
            "\t\tCASE\n" +
            "\t\t\t\t\n" +
            "\t\t\t\tWHEN zh.mc IS NULL \n" +
            "\t\t\t\tOR zh.mc = '' THEN\n" +
            "\t\t\t\t\t'贵金属、稀有分散元素矿产' ELSE zh.mc \n" +
            "\t\t\t\t\tEND AS mc,\n" +
            "\t\t\tCASE\n" +
            "\t\t\t\t\t\n" +
            "\t\t\t\t\tWHEN ks.kcdm IN ( 42201, 42202, 32006, 63701 ) THEN\n" +
            "\t\t\t\t\t'' ELSE ks.jsdxmc \n" +
            "\t\t\t\tEND AS jsdxmc,\n" +
            "\t\t\t\tks.kcdm,\n" +
            "\t\n" +
            "\t\t\t\tSUM ( CASE WHEN kc.jsdwbs != 0 THEN zy.nmbyjsl ELSE zy.nmbyksl END ) AS zyl,\n" +
            "\t\t\tMAX ( CASE WHEN kc.xsbs = 0 THEN kc.jsldw ELSE kc.ksldw END ) dw \n" +
            "\t\tFROM\n" +
            "\t\t\thisi_kshz ks\n" +
            "\t\t\tLEFT JOIN hisi_ks_zyclhz zy ON ks.nd = zy.nd \n" +
            "\t\t\tAND ks.kqbh = zy.kqbh \n" +
            "\t\t\tAND ks.djflbh = zy.djflbh \n" +
            "\t\t\tAND ks.kcdm = zy.kcdm \n" +
            "\t\t\tAND ks.tjdx = zy.tjdx\n" +
            "\t\t\tLEFT JOIN kcl_cb06_kc kc ON ks.kcdm = kc.kcdm \n" +
            "\t\t\tAND ks.tjdx = kc.tjdx\n" +
            "\t\t\tLEFT JOIN kcl_cb01_zh zh ON ( kc.kclb :: int8 / 10 * 100 ) = zh.dm :: int8 \n" +
            "\t\tWHERE\n" +
            "\t\t\tks.nd = #{nd}\n" +
            "\t\t\tAND ks.sbbsm <> 3 \n" +
            "\t\t\tAND ks.kczh NOT IN ( '单一矿产', '主要矿产' ) \n" +
            "-- \t\t\tAND ks.kcdm = 42202 \n" +
            "\t\t\tAND zy.zycllx IN ( 'TM', 'KZ', 'TD' ) \n" +
            "\t\tGROUP BY\n" +
            "\t\t\tks.nd,\n" +
            "\t\t\tks.kqbh,\n" +
            "\t\t\tks.kcmc,\n" +
            "\t\t\tks.jsdxmc,\n" +
            "\t\t\t ks.kczh,\n" +
            "\t\t\t ks.tjdx,\n" +
            "\t\tCASE\n" +
            "\t\t\t\t\n" +
            "\t\t\t\tWHEN zh.mc IS NULL \n" +
            "\t\t\t\tOR zh.mc = '' THEN\n" +
            "\t\t\t\t\t'700400' ELSE zh.dm \n" +
            "\t\t\t\t\tEND,\n" +
            "\t\t\tCASE\n" +
            "\t\t\t\t\t\n" +
            "\t\t\t\t\tWHEN zh.mc IS NULL \n" +
            "\t\t\t\t\tOR zh.mc = '' THEN\n" +
            "\t\t\t\t\t\t'贵金属、稀有分散元素矿产' ELSE zh.mc \n" +
            "\t\t\t\t\t\tEND,\n" +
            "\t\t\t\t\tks.kcdm\n" +
            "\t\t\t\t\t),\n" +
            "\t\t\t\t\tDATA AS (\n" +
            "\t\t\t\t\tSELECT\n" +
            "\t\t\t\t\tCASE\n" +
            "\t\t\t\t\t\t\t\n" +
            "\t\t\t\t\t\tWHEN\n" +
            "\t\t\t\t\t\t\tmc = '能源矿产' THEN\n" +
            "\t\t\t\t\t\t\t\t'能源矿产' \n" +
            "\t\t\t\t\t\t\t\tWHEN mc IN ( '黑色金属矿产', '有色金属矿产', '贵金属、稀有分散元素矿产' ) THEN\n" +
            "\t\t\t\t\t\t\t\t'金属矿产' ELSE'非金属矿产' \n" +
            "\t\t\t\t\t\t\tEND AS kcdl,\n" +
            "\t\t\t\t\t\t\tmc AS kczl,\n" +
            "\t\t\t\t\t\t\t( CASE WHEN jsdxmc <> '' THEN CONCAT ( kcmc, ' (', jsdxmc, ')' ) ELSE kcmc END ) AS kcmc,\n" +
            "\t\t\t\t\t\t\tSUM ( zyl ) AS zyl,\n" +
            "\t\t\t\t\t\t\tMAX ( dw ) AS dw,\n" +
            "\t\t\t\t\t\t\tkcdm,\n" +
            "\t\t\t\t\t\t\tkqbh,\n" +
            "\t\t\t\t\t\t\ttjdx,\n" +
            "\t\t\t\t\t\t\tkczh \n" +
            "\t\t\t\t\t\tFROM\n" +
            "\t\t\t\t\t\t\tbase \n" +
            "\t\t\t\t\t\tGROUP BY\n" +
            "\t\t\t\t\t\tCASE\n" +
            "\t\t\t\t\t\t\t\t\n" +
            "\t\t\t\t\t\t\t\tWHEN mc = '能源矿产' THEN\n" +
            "\t\t\t\t\t\t\t\t'能源矿产' \n" +
            "\t\t\t\t\t\t\t\tWHEN mc IN ( '黑色金属矿产', '有色金属矿产', '贵金属、稀有分散元素矿产' ) THEN\n" +
            "\t\t\t\t\t\t\t\t'金属矿产' ELSE'非金属矿产' \n" +
            "\t\t\t\t\t\t\tEND,\n" +
            "\t\t\t\t\t\t\tmc,\n" +
            "\t\t\t\t\t\t\ttjdx,\n" +
            "\t\t\t\t\t\t\tkczh,\n" +
            "\t\t\t\t\t\tCASE\n" +
            "\t\t\t\t\t\t\t\t\n" +
            "\t\t\t\t\t\t\t\tWHEN jsdxmc <> '' THEN\n" +
            "\t\t\t\t\t\t\t\tCONCAT ( kcmc, ' (', jsdxmc, ')' ) ELSE kcmc \n" +
            "\t\t\t\t\t\t\tEND,\n" +
            "\t\t\t\t\t\t\tkcdm,\n" +
            "\t\t\t\t\t\t\tkqbh \n" +
            "\t\t\t\t\t\tORDER BY\n" +
            "\t\t\t\t\t\t\tkcdm,\n" +
            "\t\t\t\t\t\t\ttjdx \n" +
            "\t\t\t\t\t\t\t\n" +
            "\t\t\t\t\t\t) SELECT\n" +
            "\t\t\t\t\t\tkcdl,\n" +
            "\t\t\t\t\t\tkczl AS kclb,\n" +
            "\t\t\t\t\t\tkcmc,\n" +
            "\t\t\t\t\t\tdw,\n" +
            "\t\t\t\t\t\tkcdm,\n" +
            "\t\t\t\t\t\n" +
            "\t\t\t\t\t\tCOUNT ( * ) AS kcds,\n" +
            "\t\t\t\t\t\tCAST ( SUM ( zyl ) AS DECIMAL ( 18, 2 ) ) AS zyl \n" +
            "\t\t\t\t\tFROM\n" +
            "\t\t\t\t\tDATA \n" +
            "\t\t\t\t\tGROUP BY\n" +
            "\t\t\t\t\t\tkcdl,\n" +
            "\t\t\t\t\t\tkczl,\n" +
            "\t\t\t\t\t\tkcmc,\n" +
            "\t\t\t\t\t\tdw,\n" +
            "\t\t\t\t\t\tkcdm\n" +
            "\t\t\t\t\t\n" +
            "\t\t\t\t\tORDER BY\n" +
            "\t\t\t\t\tkcdm\n")
    List<GbskcVo> getNbfbEight(Integer nd);
}
